package com.wxd.framework.common.office;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;

public class ExcelWriter {

	// 设置cell编码解决中文高位字节截断
	private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
	// 定制浮点数格式
	private static String NUMBER_FORMAT = "#,##0.00";
	// 定制日期格式
	private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
	private OutputStream outputStream = null;
	private Workbook workbook = null;
	private Sheet sheet = null;
	private Row row = null;
	
	public ExcelWriter(){
	}
	
	public ExcelWriter(OutputStream outputStream){
		this.outputStream = outputStream;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet();
	}
	
	/**
	 * 导出Excel文件
	 * @throws IOException
	 */
	public void export() throws IOException{
		try{
			workbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		}catch(FileNotFoundException ex){
			throw ex;
		}catch (IOException ex) {
			throw ex;
		}
	}
	
	/**
	 * 设置列宽
	 * @param index 列号
	 * @param width 宽度
	 */
	public void setColumnWidth(int index ,int width){
		sheet.setColumnWidth(index, width); 
	}
	
	/**
	 * 增加一行
	 * @param index 行号           
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}
	
	/**
	 * 设置单元格(int)
	 * @param index 列号          
	 * @param value 单元格填充值           
	 */
	public void setCell(int index, int value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格(double)
	 * @param index 列号          
	 * @param value 单元格填充值           
	 */
	public void setCell(int index, double value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
	}

	/**
	 * 设置单元格(String)
	 * @param index 列号          
	 * @param value 单元格填充值           
	 */
	public void setCell(int index, String value) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		//cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value);
	}
	
	/**
	 * 设置单元格(日期)
	 * @param index 列号         
	 * @param value 单元格填充值           
	 */
	public void setCell(int index, Calendar value) {
		Cell cell = this.row.createCell((short) index);
		//cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value.getTime());
		CellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
		cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
	}
	
	/**
	 * 设置单元格
	 * @param index 列号           
	 * @param a 合并单元格的行数
	 * @param b 合并单元格的列数1
	 * a,b确定要合并的初始单元格
	 * @param c 合并单元格的行数
	 * @param d 合并单元格的列数2
	 * c,d确定要合并的结束单元格          
	 * @param value 单元格填充值
	 *            
	 */
	public void setCell(int index, String value, int a,int b,int c, int d) {
		Cell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		sheet.addMergedRegion(new CellRangeAddress(a,(short)b,c,(short)d));
		cell.setCellValue(value);
	}
	
}
